/*-+-*****************************************************************************
 Copyright (C) 2009-2012 Oceanside Software Corporation (R)
 Dallas, Texas

 Author       : Jason T. Brower
 Purpose      : This file is part of a point-of-sale software package
                originally written for Table-Service Restaurants.
              
 Date         : July 8th of 2012
              
 History      : The application that this software file belongs to was 
                written over a 3-Year period.  The first version was written
                in C# .Net 3.5 using Windows Forms.  Being new to .Net at
                the time, I soon learned of Windows Presentation Foundation,
                liked the graphics capabilities and databinding so I
                migrated to WPF.  When the .Net 4.0 framework was released
                the application was migrated from 3.5 to 4.0. Now today,
                .Net 4.5 RC is out and I was already in process of migrating
                before I made this public.
                
Excuse Me     : Please excuse the coding style, it is not representative of
                my typical work.  During the three years I was under a very
                tight budget, constantly running out of funding and was 
                working 18 hour days hyped up on 5-Hour Energy.  The 
                design is not textbook.  It is monolythic because of
                previous issues I will not get into.  There are few comments
                in the code and the ones that are there may be incorrect.
                
What it is not: This is not the exact version that I took through PA-DSS 2.0
                nor is it the exact version running at my customer sites.
                I removed some details for security purposes including some
                encryption algorithms.  The reporting was removed because
                I was including a third party library that cannot be 
                published with this Open Source.
                
What it is    : This application has been running at high volume restaraunts
                for over a year.  It never freezes, crashes or does anything
                unexpected.  So long as it is built, installed and configured
                correctly, it is a strong system that we can all enhance.

Contact       : Please note that I can no longer work for free.  However, you
                can reach me at information@oceansidesw.com.                                                      

*********************************************************************************/
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows.Controls;
using System.Data.Objects.DataClasses;

#if _LOCALDB
  using Oceanside.LocalDB;
#else
using Oceanside.RemoteDB;
#endif

#if _LOCALDB
  namespace Oceanside.LocalDB
#else
namespace Oceanside.RemoteDB
#endif
{
    /// <summary>
    /// Contains functions and static objects that are common to all EF classes.
    /// </summary>
    public partial class DominicaEntities
    {
        public const Int64 CONST_ALL_DB_OBJECTS_WILDCARD = -1;
        public const int CONST_MIN_BOH_PASSWORD_LEN = 7;
        public const int CONST_MAX_LOGIN_ATTEMPTS_BOH = 6;

        public static TRANSACTION GetPaymentAt(int index, EntityCollection<TRANSACTION> coll)
        {
            TRANSACTION obj = null;
            if (coll != null)
            {
                obj = coll.Skip(index).First();
            }
            return obj;
        }

        public static SALE GetVolProductAt(int index, EntityCollection<SALE> coll)
        {
            SALE obj = null;
            if (coll != null)
            {
                obj = coll.Skip(index).First();
            }
            return obj;
        }

        private static string sqlDbName = "";

        public static string SqlDbName
        {
            get { return sqlDbName; }
            set { sqlDbName = value; }
        }
        private static string sqlDbInstanceName = "";

        public static string SqlDbInstanceName
        {
            get { return sqlDbInstanceName; }
            set { sqlDbInstanceName = value; }
        }
        private static string sqlDbServerIp = "";

        public static string SqlDbServerIp
        {
            get { return sqlDbServerIp; }
            set { sqlDbServerIp = value; }
        }
        private static string sqlDbPword = "";

        public static string SqlDbPword
        {
            get { return sqlDbPword; }
            set { sqlDbPword = value; }
        }
        private static string sqlDbUser = "";

        public static string SqlDbUser
        {
            get { return sqlDbUser; }
            set { sqlDbUser = value; }
        }

        public static void GetDatabaseConnectionValues()
        {
            string sysConfig = Oceanside.Driver.UnDrive();
            string[] values = sysConfig.Trim().Split('\n');
#if !_LOCALDB
            Oceanside.RemoteDB.DominicaEntities.SqlDbServerIp = values[0];
            Oceanside.RemoteDB.DominicaEntities.SqlDbInstanceName = values[1];
            Oceanside.RemoteDB.DominicaEntities.SqlDbUser = values[2];
            Oceanside.RemoteDB.DominicaEntities.SqlDbPword = values[3];
            Oceanside.RemoteDB.DominicaEntities.SqlDbName = values[4];
#else
            Oceanside.LocalDB.DominicaEntities.SqlDbServerIp = values[0];
            Oceanside.LocalDB.DominicaEntities.SqlDbInstanceName = values[1];
            Oceanside.LocalDB.DominicaEntities.SqlDbUser = values[2];
            Oceanside.LocalDB.DominicaEntities.SqlDbPword = values[3];
            Oceanside.LocalDB.DominicaEntities.SqlDbName = values[4];
#endif
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        static string _cachedMSSQL2008EfConnectionString = null;
        static string _cachedMSSQL2008TestEfConnectionString = null;
        public static string GetEfConString(bool isTest = false)
        {
            if (Constants.IsTrainingMode || isTest)
            {
                if (_cachedMSSQL2008TestEfConnectionString == null)
                {
                    GetDatabaseConnectionValues();
                    if (Constants.IsLocalDbMode)
                    {
                        // CreateContext the connection string builder for the
                        // underlying provider.
                        SqlConnectionStringBuilder sqlBuilder =
                            new SqlConnectionStringBuilder();

                        // Set the properties for the data source.
                        sqlBuilder.DataSource = Directory.GetCurrentDirectory() + "\\Database\\Embedded\\CompactD.sdf";
                        sqlBuilder.Password = SqlDbPword;

                        // CreateContext the EntityConnectionStringBuilder.
                        EntityConnectionStringBuilder entityBuilder =
                            new EntityConnectionStringBuilder();

                        //Set the provider name.
                        entityBuilder.Provider = CONST_LOCAL_PROVIDER;

                        // Set the provider-specific connection string.
                        entityBuilder.ProviderConnectionString = sqlBuilder.ToString();

                        // Set the Metadata location.
                        entityBuilder.Metadata = string.Format("res://*/Database.LocalDominicaModel.csdl|res://*/Database.LocalDominicaModel.ssdl|res://*/Database.LocalDominicaModel.msl");

                        _cachedMSSQL2008TestEfConnectionString = entityBuilder.ToString();
                    }
                    else
                    {
                        // CreateContext the connection string builder for the
                        // underlying provider.
                        SqlConnectionStringBuilder sqlBuilder =
                            new SqlConnectionStringBuilder();

                        // Set the properties for the data source.
#if !_LOCALDB
                        sqlBuilder.DataSource = Oceanside.RemoteDB.DominicaEntities.SqlDbServerIp + "\\" + Oceanside.RemoteDB.DominicaEntities.SqlDbInstanceName;
                        sqlBuilder.InitialCatalog = Oceanside.RemoteDB.DominicaEntities.SqlDbName + "Test";
                        sqlBuilder.IntegratedSecurity = CONST_USEWINDOWS_AUTH;
                        sqlBuilder.UserID = Oceanside.RemoteDB.DominicaEntities.SqlDbUser;
                        sqlBuilder.Password = Oceanside.RemoteDB.DominicaEntities.SqlDbPword;
#else
                        sqlBuilder.DataSource = Oceanside.LocalDB.DominicaEntities.SqlDbServerIp + "\\" + Oceanside.LocalDB.DominicaEntities.SqlDbInstanceName;
                        sqlBuilder.InitialCatalog = Oceanside.LocalDB.DominicaEntities.SqlDbName + "Test";
                        sqlBuilder.IntegratedSecurity = CONST_USEWINDOWS_AUTH;
                        sqlBuilder.UserID = Oceanside.LocalDB.DominicaEntities.SqlDbUser;
                        sqlBuilder.Password = Oceanside.LocalDB.DominicaEntities.SqlDbPword;
#endif
                        ///This little number here haunted me for a long time.  LINQ queries can execute delayed and
                        ///leave you with exceptions that are extremely timing specific.
                        sqlBuilder.MultipleActiveResultSets = true;

                        // CreateContext the EntityConnectionStringBuilder.
                        EntityConnectionStringBuilder entityBuilder =
                            new EntityConnectionStringBuilder();

                        //Set the provider name.
                        entityBuilder.Provider = CONST_PROVIDER;

                        // Set the provider-specific connection string.
                        entityBuilder.ProviderConnectionString = sqlBuilder.ToString();

                        // Set the Metadata location.
                        entityBuilder.Metadata = string.Format("res://*/Database.RemoteDominicaModel.csdl|res://*/Database.RemoteDominicaModel.ssdl|res://*/Database.RemoteDominicaModel.msl");

                        _cachedMSSQL2008TestEfConnectionString = entityBuilder.ToString();

                    }

                }
                return _cachedMSSQL2008TestEfConnectionString;
            }
            else
            {
                if (_cachedMSSQL2008EfConnectionString == null)
                {
                    GetDatabaseConnectionValues();

                    // CreateContext the connection string builder for the
                    // underlying provider.
                    SqlConnectionStringBuilder sqlBuilder =
                        new SqlConnectionStringBuilder();

                    // Set the properties for the data source.
                    sqlBuilder.DataSource = SqlDbServerIp + "\\" + SqlDbInstanceName;

                    sqlBuilder.InitialCatalog = SqlDbName;
                    sqlBuilder.IntegratedSecurity = CONST_USEWINDOWS_AUTH;
                    sqlBuilder.UserID = SqlDbUser;
                    sqlBuilder.Password = SqlDbPword;

                    ///This little number here haunted me for a long time.  LINQ queries can execute delayed and
                    ///leave you with exceptions that are extremely timing specific.
                    sqlBuilder.MultipleActiveResultSets = true;

                    // CreateContext the EntityConnectionStringBuilder.
                    EntityConnectionStringBuilder entityBuilder =
                        new EntityConnectionStringBuilder();

                    //Set the provider name.
                    entityBuilder.Provider = CONST_PROVIDER;

                    // Set the provider-specific connection string.
                    entityBuilder.ProviderConnectionString = sqlBuilder.ToString();

                    // Set the Metadata location.
                    entityBuilder.Metadata = string.Format("res://*/Database.RemoteDominicaModel.csdl|res://*/Database.RemoteDominicaModel.ssdl|res://*/Database.RemoteDominicaModel.msl");

                    _cachedMSSQL2008EfConnectionString = entityBuilder.ToString();
                }
                return _cachedMSSQL2008EfConnectionString;
            }
        }


        #region Public
        #region Public Types
        public delegate void TimePunchSaveFailedType();
        public delegate void SaveChangesFailedCallbackDelType();
        public delegate void UserCheckoutDelegateType(bool didPass, SHIFT co);
        public delegate void HouseCheckoutDelegateType(bool didPass, ref BUSINESS_DAY co);


        /// <summary>
        /// It is possible to store customers in a database, but we are not doing that yet.
        /// </summary>
        public static Guid CONST_NO_CUSTOMER_DATA = new Guid("EEFC3B44-98C7-497C-B8F8-98DFAC2B062F");

        #endregion  Public Types

        #region Public Static Variables and Properties
        #endregion  Public Static Variables

        #region Public Static Functions

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static T DetachEntireGraph<T>(T obj)
        {
            using (var stream = new MemoryStream())
            {
                T local;
                var formatter = new BinaryFormatter();
                formatter.Serialize(stream, obj);
                stream.Position = 0;
                local = (T)formatter.Deserialize(stream);
                return local;
            }
        }

        #endregion Public Static Functions

        #region Public Member Variables and Properties

        #endregion Member Variables

        #region Public Member Functions

        #endregion Public Member Functions
        #endregion Public

        #region FROM THE REMOTE FILE
        /// <summary>
        /// Timeouts used when communicating with the database.
        /// </summary>
        public const int CONST_NORMAL_TIMEOUT_SEC = 10;

        /// <summary>
        /// Used to mark a database error.
        /// </summary>
        public const int CONST_DBERROR = -1;

        // Specify the provider name, server and database.
        const string CONST_PROVIDER = "System.Data.SqlClient";
        const string CONST_LOCAL_PROVIDER = "System.Data.SqlServerCe.4.0";
        ///
        /// 
        /// <summary>
        /// The following connection information may need to be changed for different development 
        /// environment.
        /// </summary>
        const bool CONST_USEWINDOWS_AUTH = false;

        /// <summary>
        /// Status for setting the magnetic password of a user.
        /// </summary>
        public enum ENUM_SET_MAG_PWORD_STATUS
        {
            COMMUNICATION_ERROR,
            PWORD_CURR_IN_USE,
            SUCCESS
        }

        ///The connection string that has been built once this class is accessed by proxy of the static
        ///constructor.
        static String databaseOneConenctionString;
        public static String DatabaseOneConenctionString
        {
            get { return databaseOneConenctionString; }
            set { databaseOneConenctionString = value; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static PRODUCT GetProductIdForCompactProductId(Int64 id)
        {
            using (DominicaEntities context = new DominicaEntities(GetEfConString()))
            {
                var q2 = from PRODUCT in context.PRODUCTS where PRODUCT.Id == id select PRODUCT;
                if (q2.Count() != 0)
                {
                    return q2.FirstOrDefault();
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="context"></param>
        /// <returns></returns>
        public static ROLE GetRemoteRoleForCompactRoleId(Int64 id, DominicaEntities context)
        {
            var q2 = from ROLE in context.ROLES where ROLE.Id == id select ROLE;
            if (q2.Count() != 0)
            {
                return q2.FirstOrDefault();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="context"></param>
        /// <returns></returns>
        public static USER GetUserForCompactUserId(Int64 id, DominicaEntities context)
        {
            var q2 = from USER in context.USERS.Include("ROLES.ACCESS_LEVEL").Include("ROLES.PAYGRADE").Include("ROLES.JOB_CODE")
                     .Include("ROLES.JOB_CODE.DELIVERY_DRIVER")
                     where USER.Id == id
                         && !USER.IsTerminated
                     select USER;
            if (q2.Count() != 0)
            {
                return q2.FirstOrDefault();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public static bool BohAccountIsLocked(string username)
        {
            try
            {
                using (DominicaEntities context = new DominicaEntities(DominicaEntities.GetEfConString()))
                {
                    var q = from USER in context.USERS where USER.UserName == username && USER.IsTerminated == false select USER;

                    if (q.Count() != 0)
                    {
                        USER user = q.First();
                        if (user.BohPasswordAttempts < CONST_MAX_LOGIN_ATTEMPTS_BOH)
                        {
                            user.BohPasswordAttempts += 1;

                            if (user.BohLockoutDate != null)
                            {
                                DateTime date = ((DateTime)user.BohLockoutDate) + TimeSpan.FromMinutes(30.0);

                                if (DateTime.Now > date)
                                {
                                    user.BohLockoutDate = DateTime.Now;
                                    user.BohPasswordAttempts = 1;
                                    context.SaveChanges();
                                    return false;
                                }
                            }

                            context.SaveChanges();
                            return false;
                        }
                        else
                        {
                            if (user.BohLockoutDate != null)
                            {
                                DateTime date = ((DateTime)user.BohLockoutDate) + TimeSpan.FromMinutes(30.0);

                                if (DateTime.Now > date)
                                {
                                    user.BohLockoutDate = DateTime.Now;
                                    user.BohPasswordAttempts = 0;
                                    context.SaveChanges();
                                    return false;
                                }
                                else
                                {
                                    user.BohLockoutDate = DateTime.Now;
                                    user.BohPasswordAttempts += 1;
                                    context.SaveChanges();
                                    return true;
                                }
                            }
                            else
                            {
                                user.BohLockoutDate = DateTime.Now;
                                context.SaveChanges();
                                return true;
                            }
                        }
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch (Exception e)
            {
                Oceanside.Logging.LogException(e);
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="username"></param>
        /// <param name="pword"></param>
        /// <returns></returns>
        public static USER ValidateBohLogin(String username, String pword, DominicaEntities context)
        {
            USER loginUser = null;

            try
            {
                ///Create the query object.
                var query = from USER in context.USERS
                                .Include("ROLES.ACCESS_LEVEL")
                                .Include("ROLES.PAYGRADE")
                                .Include("ROLES.JOB_CODE")
                            where (USER.UserName == username && !USER.IsTerminated)
                            select USER;

                ///This call will actually connect to the database and pull the data down.
                if (query.Count() != 0)
                {
                    loginUser = query.First();
                    string deCryptPword = Sec.DecryptBytes(loginUser.LoginBohPassword);
                    if (!pword.Equals(deCryptPword))
                    {
                        loginUser = null;
                    }
                }
            }
            catch (Exception e)
            {
                System.Windows.MessageBox.Show(e.ToString());
            }

            return loginUser;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="username"></param>
        /// <param name="pword"></param>
        /// <returns></returns>
        public static bool SetBohPassword(String username, String pword, String pwordVerify, String email, String question, String answer)
        {
            if (String.IsNullOrEmpty(username) || String.IsNullOrEmpty(pword) || String.IsNullOrEmpty(pwordVerify) || String.IsNullOrEmpty(email) ||
                String.IsNullOrEmpty(question) || String.IsNullOrEmpty(answer))
            {
                return false;
            }
            else
            {
                if (VerifyBohPasswordValid(pword, pwordVerify, username) != PasswordState.Accept)
                {
                    return false;
                }
                else if (!(email.Contains('@') && email.Contains('.')))
                {
                    return false;
                }
                else
                {
                    try
                    {
                        using (DominicaEntities context = new DominicaEntities(DominicaEntities.GetEfConString()))
                        {
                            ///Create the query object.
                            var query = from USER in context.USERS where (USER.UserName == username && !USER.IsTerminated) select USER;

                            ///This call will actually connect to the database and pull the data down.
                            if (query.Count() != 0)
                            {
                                USER user = query.First();

                                user.UserName = username;
                                user.OldBohPassword4 = user.OldBohPassword3;
                                user.OldBohPassword3 = user.OldBohPassword2;
                                user.OldBohPassword2 = user.OldBohPassword1;
                                user.OldBohPassword1 = user.LoginBohPassword;
                                user.LoginBohPassword = Sec.EncryptString(pword);
                                user.BohSecretQuestion = question;
                                user.BohPasswordAttempts = 0;
                                user.BohSecretAnswer = answer;
                                user.EmailAddress = email;
                                user.BohPasswordDate = DateTime.Now;
                                context.SaveChanges();
                                return true;
                            }
                            else
                            {
                                return false;
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        Oceanside.Logging.LogException(e);
                        return false;
                    }
                }
            }
        }

        public enum PasswordState
        {
            InUseError,
            SevenCharsError,
            DontMatchError,
            AlphaNumericError,
            Accept
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="pword"></param>
        /// <param name="pwordVerify"></param>
        /// <returns></returns>
        public static PasswordState VerifyBohPasswordValid(string pword, string pwordVerify, string username)
        {
            if (string.IsNullOrEmpty(pword) || string.IsNullOrEmpty(pwordVerify))
            {
                return PasswordState.DontMatchError;
            }
            else if (!pword.Equals(pwordVerify))
            {
                return PasswordState.DontMatchError;
            }
            else if (!(pword.Length >= CONST_MIN_BOH_PASSWORD_LEN))
            {
                return PasswordState.SevenCharsError;
            }
            else
            {
                bool foundAlpha = false;
                bool foundNumeric = false;

                foreach (char c in pword)
                {
                    if (char.IsDigit(c))
                    {
                        foundNumeric = true;
                    }
                    else if (char.IsLetter(c))
                    {
                        foundAlpha = true;
                    }
                }

                if (!foundNumeric || !foundAlpha)
                {
                    return PasswordState.AlphaNumericError;
                }
                else
                {
                    using (DominicaEntities context = new DominicaEntities(GetEfConString()))
                    {
                        var query = from USER in context.USERS where (USER.UserName == username && !USER.IsTerminated) select USER;

                        ///This call will actually connect to the database and pull the data down.
                        if (query.Count() != 0)
                        {
                            USER user = query.First();

                            string bohP1 = Sec.DecryptBytes(user.OldBohPassword1);
                            string bohP2 = Sec.DecryptBytes(user.OldBohPassword1);
                            string bohP3 = Sec.DecryptBytes(user.OldBohPassword1);
                            string bohP4 = Sec.DecryptBytes(user.OldBohPassword1);

                            if (bohP1.Equals(pword) || bohP2.Equals(pword) || bohP3.Equals(pword) || bohP4.Equals(pword))
                            {
                                return PasswordState.InUseError;
                            }
                            else
                            {
                                return PasswordState.Accept;
                            }
                        }
                        else
                        {
                            return PasswordState.Accept;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// If the user is using the FOH keypad as a potential login type, we need to assure it is
        /// not already in use.
        /// </summary>
        /// <returns></returns>
        public static bool FohKeypadPasswordIsAvailable(USER user, String pw, DominicaEntities context)
        {
            Boolean numericPasswordAvailable = false;

            if (pw != null)
            {
                if (!pw.Equals("1111") && !pw.Equals("2222") && !pw.Equals("3333") && !pw.Equals("4444"))
                {
                    var query = from USER in context.USERS
                                where (USER.LoginKeyPassword == pw && USER.Id != user.Id && !USER.IsTerminated)
                                select USER;

                    try
                    {
                        if (query.Count() == 0)
                        {
                            numericPasswordAvailable = true;
                        }
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
            return numericPasswordAvailable;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="aboutToTerminateUser"></param>
        public static void TerminateUser(USER aboutToTerminateUser, DominicaEntities context)
        {
            try
            {
                ///Terminate the user.
                aboutToTerminateUser.IsTerminated = true;

                ///Save all changes and hope we don't throw an exception.
                context.SaveChanges(SaveOptions.AcceptAllChangesAfterSave | SaveOptions.DetectChangesBeforeSave);
            }
            catch
            {
                throw;
            }
        }


        /// <summary>
        /// Returns a list of menus.
        /// </summary>
        /// <returns></returns>
        public static List<MENU> GetMenus(DominicaEntities context)
        {
            List<MENU> menuList = null;

            try
            {
                var query = from MENU in context.MENUS where MENU.IsDeleted == false select MENU;
                menuList = query.ToList();
            }
            catch (Exception e)
            {
                System.Windows.MessageBox.Show(e.ToString());
            }

            return menuList;
        }

        /// <summary>
        /// This function is used on the PageUserRoles.xaml file.
        /// </summary>
        /// <returns></returns>
        public static void UpdateUsernameComboBinding(ComboBox cb, DominicaEntities context)
        {
            if (cb.ItemsSource == null)
            {
                var query = from USER in context.USERS.Include("ROLES")
                                .Include("ROLES.PAYGRADE")
                                .Include("ROLES.JOB_CODE")
                                .Include("ROLES.ACCESS_LEVEL")
                            where !USER.IsTerminated
                            && USER.UserName != null
                            && !USER.FirstName.Contains("Training")
                            orderby USER.LastName, USER.FirstName
                            select USER;

                if (query.Count() != 0)
                {
                    cb.ItemsSource = query.AsEnumerable();
                    cb.SelectedIndex = 0;
                }
                else
                {
                    cb.ItemsSource = null;
                }
            }
        }

        /// <summary>
        /// Must use this because cannot bind directly to EF obect.  Why?!
        /// </summary>
        /// <returns></returns>
        public static void UpdateJobCodesComboBinding(ComboBox cb, DominicaEntities context)
        {

            var query = from JOB_CODE in context.JOB_CODES 
                        where JOB_CODE.IsDeleted == false &&
                        JOB_CODE.Name != null &&
                        !JOB_CODE.Name.Contains("Training")
                        orderby JOB_CODE.Name select JOB_CODE;

            if (query.Count() != 0)
            {
                cb.ItemsSource = query.AsEnumerable();
            }
            else
            {
                cb.ItemsSource = null;
            }
        }


        /// <summary>
        /// Must use this because cannot bind directly to EF obect.  Why?!
        /// </summary>
        /// <returns></returns>
        public static void UpdateAccessLevelsComboBinding(ComboBox cb, DominicaEntities context)
        {
            var query = from ACCESS_LEVEL in context.ACCESS_LEVELS 
                        where ACCESS_LEVEL.IsDeleted == false &&
                        ACCESS_LEVEL.Name != null &&
                        !ACCESS_LEVEL.Name.Contains("Training")
                        orderby ACCESS_LEVEL.Name select ACCESS_LEVEL;

            if (query.Count() != 0)
            {
                cb.ItemsSource = query.AsEnumerable();
            }
            else
            {
                cb.ItemsSource = null;
            }

        }

        /// <summary>
        /// Must use this because cannot bind directly to EF obect.  Why?!
        /// </summary>
        /// <returns></returns>
        public static void UpdatePayGradesComboBinding(ComboBox cb, DominicaEntities context)
        {

            var query = from PAYGRADE in context.PAYGRADES 
                        where PAYGRADE.IsDeleted == false &&
                        PAYGRADE.Name != null &&
                        !PAYGRADE.Name.Contains("Training")
                        orderby PAYGRADE.WageRate select PAYGRADE;

            if (query.Count() != 0)
            {
                cb.ItemsSource = query.AsEnumerable();
            }
            else
            {
                cb.ItemsSource = null;
            }

        }

        /// <summary>
        /// If a BOH user has not been setup yet, then allow the user to login
        /// without a username and password.
        /// </summary>
        public static Boolean HasBohUser(string connectionString)
        {
            Boolean bohLoginSet = false;
            using (DominicaEntities context = new DominicaEntities(connectionString))
            {
                try
                {
                    var configQuery = from USER in context.USERS
                                      where !USER.IsTerminated && USER.LoginBohPassword != null
                                      select USER;

                    if (configQuery.Any())
                    {
                        foreach (USER users in configQuery)
                        {
                            string deCryptPword = Sec.DecryptBytes(users.LoginBohPassword);
                            if (deCryptPword.Length >= CONST_MIN_BOH_PASSWORD_LEN)
                            {
                                bohLoginSet = true;
                            }
                        }
                    }
                }
                catch { }
            }
            return bohLoginSet;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static String GenerateAvailableFohPassword()
        {
            Random randomNumberGenerator = new Random();

            String rand = String.Concat(
                randomNumberGenerator.Next(0, 9),
                randomNumberGenerator.Next(0, 9),
                randomNumberGenerator.Next(0, 9),
                randomNumberGenerator.Next(0, 9));
            using (DominicaEntities context = new DominicaEntities(GetEfConString()))
            {
                var query = from USER in context.USERS where USER.LoginKeyPassword == rand && !USER.IsTerminated select USER;

                while (query.Count() != 0)
                {
                    rand = String.Concat(
                                          randomNumberGenerator.Next(0, 9),
                                          randomNumberGenerator.Next(0, 9),
                                          randomNumberGenerator.Next(0, 9),
                                          randomNumberGenerator.Next(0, 9)
                                          );

                    query = from USER in context.USERS where USER.LoginKeyPassword != rand && !USER.IsTerminated select USER;
                }
            }
            return rand;
        }
        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public static bool BohRequiresReset(string username)
        {
            try
            {
                using (DominicaEntities context = new DominicaEntities(DominicaEntities.GetEfConString()))
                {
                    var q = from USER in context.USERS where USER.UserName == username && USER.IsTerminated == false select USER;

                    if (q.Count() != 0)
                    {
                        USER user = q.First();
                        if (user.BohPasswordDate != null)
                        {
                            DateTime date = ((DateTime)user.BohPasswordDate) + TimeSpan.FromDays(90);

                            if (DateTime.Now >= date)
                            {
                                return true;
                            }
                        }
                        else
                        {
                            user.BohPasswordDate = DateTime.Now;
                            context.SaveChanges();
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Oceanside.Logging.LogException(e);
            }

            return false;
        }
    }
}
